package model.dao;

import java.io.File;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.SQLAdapter;
import model.bean.Acao;

public class AcaoDAO {
	private static final String SQL_SELECT_QUANTIDADE_POR_USUARIO = "SELECT ac.idsessao, COUNT(ac.idsessao) AS count FROM acao ac INNER JOIN app ap WHERE ap.idsessao = ac.idsessao AND ap.nome = ? AND ac.periodo >= ? AND ac.periodo <= ? GROUP BY ac.idsessao ORDER BY ac.idsessao ASC";
	private static final String SQL_SELECT_QUANTIDADE_POR_APLICATIVO = "SELECT ac.acao, COUNT(ac.acao) AS count FROM acao ac INNER JOIN app ap WHERE ap.idsessao = ac.idsessao AND ap.nome = ? AND ac.periodo >= ? AND ac.periodo <= ? GROUP BY ac.acao ORDER BY ac.acao ASC";
	private static SQLAdapter sqlAdapter;
	
    private static SQLAdapter getSqlAdapter(){    	
    	if(sqlAdapter == null){
    		sqlAdapter = new SQLAdapter(new File("bd/bd_config.xml"));
    	}
		return sqlAdapter;
	}
    
	public static void setSqlAdapter(String driver, String protocol, String host, String database, String username, String password){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(driver, protocol, host, database, username, password);			
		}else{
			getSqlAdapter().getDataSource().changeConnection(driver, protocol, host, database, username, password);
		}
	}
	
	public static void setSqlAdapter(File configFile){
		if(getSqlAdapter() == null){
			sqlAdapter = new SQLAdapter(configFile);
		}else{
			getSqlAdapter().getDataSource().changeConnection(configFile);
		}
	}
	
	public static List<Acao> queryQuantidadePorUsuario(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
		if(getSqlAdapter() != null){
    		List<Acao> acaoList = new ArrayList<Acao>();
        	
            PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_QUANTIDADE_POR_USUARIO);
            if(ps != null){
               	ps.setString(1, aplicacaoNome);
               	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
               	ResultSet resultSet = ps.executeQuery();
               	int cont = 0;
               	while(resultSet.next()){
               		cont++;
               		acaoList.add(new Acao("Usuário " + cont, resultSet.getString("count"), null, null, null, null));
               	}
               	getSqlAdapter().close();
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
            
            return acaoList;
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
	
	//Quantidade de acessos por aplicativo
	public static List<Acao> queryQuantidadePorAplicativo(String aplicacaoNome, String startPeriod, String endPeriod) throws SQLException, NullPointerException{
		if(getSqlAdapter() != null){
    		List<Acao> acaoList = new ArrayList<Acao>();
        	
            PreparedStatement ps = getSqlAdapter().getPreparedStatement(SQL_SELECT_QUANTIDADE_POR_APLICATIVO);
            if(ps != null){
               	ps.setString(1, aplicacaoNome);
               	ps.setString(2, startPeriod);
               	ps.setString(3, endPeriod);
               	
               	ResultSet resultSet = ps.executeQuery();
               	while(resultSet.next()){
               		acaoList.add(new Acao(resultSet.getString("ac.acao"), resultSet.getString("count"), null, null, null, null));
               	}
               	getSqlAdapter().close();
            }else{
            	throw new NullPointerException("PreparedStatement is null");
            }
            
            return acaoList;
    	}else{
    		throw new NullPointerException("The SqlAdapter is null, try to setSqlAdapter() before call to query.");
    	}
    }
    
    public static void main(String[] args){
    	try{
			List<Acao> acaoList = queryQuantidadePorUsuario("Tvilo Viewer", "2012-8-7", "2012-11-1");
			for(int i=0; i<acaoList.size(); i++){
				System.out.println(acaoList.get(i));
			}
			
			System.out.println();
			
			List<Acao> acaoList2 = queryQuantidadePorAplicativo("Tvilo Viewer", "2012-8-7", "2012-11-1");
			for(int i=0; i<acaoList2.size(); i++){
				System.out.println(acaoList2.get(i));
			}
			System.out.println("DONE");
			
		}catch(NullPointerException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}
